package common.project.epic.db;


import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Stack;

import android.app.Notification;
import android.app.NotificationManager;
import android.app.PendingIntent;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.location.Location;
import android.net.Uri;
import android.os.Environment;
import android.provider.MediaStore;
import android.util.Log;
import android.webkit.MimeTypeMap;

import common.project.epic.core.EpicVersionSettings;
import common.project.epic.ui.Epic;
import common.project.epic.ui.EpicMsgList;
import common.project.epic.ui.EpicMsgListTab;
import common.project.epic.ui.EpicShowMsg;
import common.project.epic.ui.R;

public class EpicDBAdapter {
	private static final String DATABASE_NAME = "epicDatabase.db";
	private static final int DATABASE_VERSION = 1;
	
	// database content table
	private static final String DATABASE_CONTENT_TABLE = "contentTable";
	
	// unique incremental id
	public static final String KEY_ID = "_id";
	public static final int IND_ID = 0;
	
	// columns KEY names
	public static final String MSG_ID = "Epic_message_id"; // MAC (2 [bytes]) + TIME TAG [s] since the 01/01/1970 (4 [bytes])
	public static final String MSG_EXP_TIME = "message_expiration_time"; // [s] since the 01/01/1970 (4 [bytes])
	public static final String MSG_GEO_LAT = "latitude_of_origin"; // y
	public static final String MSG_GEO_LON = "longitude_of_origin"; // x
	public static final String MSG_GEO_ALT = "altitude_of_origin"; // z
	public static final String MSG_GEO_SPAN = "message_geo_span"; // granularity: [tens of meters], 0 value for infinity

	public static final String MSG_CATEGORY_GENERAL = "category_general";
	public static final String MSG_CATEGORY_GAMES = "category_games";
	public static final String MSG_CATEGORY_ADVERTISEMENTS = "category_advertisements";
	public static final String MSG_CATEGORY_SUGGESTIONS = "category_suggestions";
	public static final String MSG_CATEGORY_EVENTS = "category_events";
	public static final String MSG_CATEGORY_6 = "category_6";
	public static final String MSG_CATEGORY_7 = "category_7";
	public static final String MSG_CATEGORY_8 = "category_8";
	public static final String MSG_CATEGORY_9 = "category_9";
	public static final String MSG_CATEGORY_10 = "category_10";
	public static final String MSG_CATEGORY_11 = "category_11";
	public static final String MSG_CATEGORY_12 = "category_12";
	public static final String MSG_CATEGORY_13 = "category_13";
	public static final String MSG_CATEGORY_14 = "category_14";
	public static final String MSG_CATEGORY_15 = "category_15";
	public static final String MSG_CATEGORY_16 = "category_16";
	
	public static final String MSG_CREATOR_ID = "creator_account_id"; // msg creator user account
	public static final String MSG_TITLE = "message_title";
	public static final String MSG_CONTENT = "message_content";
	public static final String MSG_EXTRA_TYPE = "extra_type"; // "file_extension_1,...,file_extension_n"   
	public static final String MSG_EXTRA_CONTENT = "extra_content"; // "file_path_1,...,file_path_n"
	
	public static final String MSG_HOP_COUNTER = "hop_counter"; // to increment by 1 at content reception, 0 if just created
	
	public static final String MSG_DEL_ID = "delivery_account_id"; // who delivered the message to me, null if I'm the creator
	public static final String MSG_DEL_TIME = "delivery_time"; // when the message was delivered to me, message birthday if I'm the creator
	public static final String MSG_DEL_LAT = "delivery_lat"; // where (latitude) the message was delivered to me, MSG_GEO_LAT if I'm the creator
	public static final String MSG_DEL_LON = "delivery_lon"; // where (longitude) the message was delivered to me, MSG_GEO_LON if I'm the creator
	public static final String MSG_DEL_ALT = "delivery_alt"; // where (altitude) the message was delivered to me, MSG_GEO_ALT if I'm the creator
	
	public static final String USER_ACCOUNT_ID = "user_account_id"; // current logged in user
	
	public static final String MSG_READ_UNREAD = "message_read_unread"; // 1 = read / 0 = unread
	public static final String MSG_DELETED = "message_deleted"; // 1 = deleted / 0 = not deleted
	public static final String SYNCH_NOTSYNCH = "synch_notsynch"; // 1 = synch / 0 = not synch
	public static final String UPDATED_NOTUPDATED = "updated_notupdated"; // 1 = updated / 0 = not updated
	public static final String DEFERRED_SENDING= "deferred_sending";// 1 = deferred sending (not yet sent) / 0 = msg is located and ready to be sent
	
	// TODO new DB columns
	public static final String LTC_TIME = "last_tx_copy_time"; // instant of last transmitted copy of this msg ([s])
	public static final String TC_NUMBER = "number_of_tx_copies"; // number of transmitted copies of this msg
    public static final String MSG_COPY_RATE = "message_received_copy_rate"; // received msg rate for this particular copy
    public static final String MY_RATE = "my_rate"; // if user changes my_rate value the flag SYNCH_NOTSYNCH should be set to 0
    public static final String CREATOR_NICK_NAME = "creator_nick_name"; // message creator nick name
    
    // TODO approximate message size [bytes]
    public static final String MSG_SIZE = "message_size"; // approximate message size [bytes]
    
    // TODO check for CRC required fields 
    // CRC(MSG_ID, TIME(expiration), GEO(x,y,z,span), CATEGORIES, CREATOR_ID, TITLE, CONTENT, attachments byte stream)
    public static final String CRC_CHECK = "crc_check"; // to verify the message integrity
	
    // content packet fixed length: pck fields' length [byte]
	private static final int CONTENT_PACKET_FIXED_LENGTH = 97;
	
	private NotificationManager mNotificationManager;
	private Notification notification;
	private Intent notificationIntent;
	private PendingIntent contentIntent;
	
	// database creation sql statement
    private static final String DATABASE_CREATE_CONTENT_TABLE= "create table " + DATABASE_CONTENT_TABLE + 
    " (" + 
    KEY_ID + " integer primary key autoincrement, " + 
    MSG_ID + " TEXT, " + 
    MSG_EXP_TIME + " INTEGER, " +
	MSG_GEO_LAT + " REAL, " +
	MSG_GEO_LON + " REAL, " +
	MSG_GEO_ALT + " REAL, " +
	MSG_GEO_SPAN + " INTEGER, " +
	
	MSG_CATEGORY_GENERAL + " BOOLEAN, " +
	MSG_CATEGORY_GAMES + " BOOLEAN, " +
	MSG_CATEGORY_ADVERTISEMENTS + " BOOLEAN, " +
	MSG_CATEGORY_SUGGESTIONS + " BOOLEAN, " +
	MSG_CATEGORY_EVENTS + " BOOLEAN, " +
	MSG_CATEGORY_6 + " BOOLEAN, " +
	MSG_CATEGORY_7 + " BOOLEAN, " +
	MSG_CATEGORY_8 + " BOOLEAN, " +
	MSG_CATEGORY_9 + " BOOLEAN, " +
	MSG_CATEGORY_10 + " BOOLEAN, " +
	MSG_CATEGORY_11 + " BOOLEAN, " +
	MSG_CATEGORY_12 + " BOOLEAN, " +
	MSG_CATEGORY_13 + " BOOLEAN, " +
	MSG_CATEGORY_14 + " BOOLEAN, " +
	MSG_CATEGORY_15 + " BOOLEAN, " +
	MSG_CATEGORY_16 + " BOOLEAN, " +
	
	MSG_CREATOR_ID + " TEXT, " +
	MSG_TITLE + " TEXT, " +
	MSG_CONTENT + " TEXT, " +
	MSG_EXTRA_TYPE + " TEXT, " +
	MSG_EXTRA_CONTENT + " TEXT, " +
	
	MSG_HOP_COUNTER + " INTEGER, " +
	
	MSG_DEL_ID + " TEXT, " +
	MSG_DEL_TIME + " INTEGER, " +
	MSG_DEL_LAT + " REAL, " +
	MSG_DEL_LON + " REAL, " +
	MSG_DEL_ALT + " REAL, " +
	
	USER_ACCOUNT_ID + " TEXT, " +
	MSG_READ_UNREAD + " BOOLEAN," +
	MSG_DELETED + " BOOLEAN," +
	SYNCH_NOTSYNCH + " BOOLEAN," +
	UPDATED_NOTUPDATED + " BOOLEAN," +
	DEFERRED_SENDING + " BOOLEAN," +
	LTC_TIME + " INTEGER," +
	TC_NUMBER + " INTEGER," +
    MSG_COPY_RATE + " REAL," +
    MY_RATE + " REAL," +
    CREATOR_NICK_NAME + " TEXT," +
    
    MSG_SIZE + " INTEGER," +
    
    CRC_CHECK + " INTEGER);";
	
    
    // variable to hold the database instance
    private SQLiteDatabase db;
    // context of the application using the database
    private Context context;
    // database open/update helper
    private EpicDbHelper dbHelper;
	
    
    // EpicDBAdapter constructor: dbHelper extends SQLiteOpenHelper
    public EpicDBAdapter(Context context) {
    	this.context = context;
    	dbHelper = new EpicDbHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    // Method to open the DB
    public EpicDBAdapter open() throws SQLException {
    	try {
    		db = dbHelper.getWritableDatabase();
    		Log.d("EpicDBAdapter", "writable and readable DB");
    	}
    	catch(SQLiteException ex) {
    		db = dbHelper.getReadableDatabase();
    		Log.d("EpicDBAdapter", "only readable DB");
    	}
    	return this;
    }

    // Method to close the DB    
    public void close() {
    	db.close();
    	Log.d("EpicDBAdapter", "DB closed");
    }   
    
    private static class EpicDbHelper extends SQLiteOpenHelper {
    	public EpicDbHelper(Context context, String name, CursorFactory factory, int version) {
    		super(context, name, factory, version);
    	}
    	@Override
    	public void onCreate(SQLiteDatabase _db) {
    		Log.w("EpicDBAdapter", "onCreate method of EpicDbHelper class " +
    				"(called when no DB exists in disk and the helper class need to create a new one)");
    		_db.execSQL(DATABASE_CREATE_CONTENT_TABLE);
    	}
    	@Override
    	public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
    		Log.w("EpicDBAdapter", "Upgrading from version " + _oldVersion + " to " + _newVersion + ", which will destroy all old data");
    		// the simplest case is to drop the old table and create a new one
    		_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_CONTENT_TABLE);
    		// create a new one
    		onCreate(_db);
    	}
    }
	
    // ADD CONTENT
    public long addContent(String msgId, long expTime, double msgLat, double msgLon, double msgAlt, int geoSpan, 
    		String msgCategories, String creatorId, 
    		String title, String content, String attachExt, String attachPath,
    		int hop_counter, 
    		String msg_del_id, long msg_del_time, double msg_del_lat, double msg_del_lon, double msg_del_alt, 
    		String user_account_id, 
    		boolean readUnread, boolean deletedNotdeleted, boolean synchNotsynch, boolean updatedNotupdated,
    		boolean deferred_sending, 
    		long ltcTime, int tcNumber, 
    		double msgRate,
    		String creatorNick, double myRate, int msgSize, long crc) {
    	ContentValues newContent = new ContentValues();
    	
    	newContent.put(MSG_ID, msgId);
    	newContent.put(MSG_EXP_TIME, expTime);
    	newContent.put(MSG_GEO_LAT, msgLat);
    	newContent.put(MSG_GEO_LON, msgLon);
    	newContent.put(MSG_GEO_ALT, msgAlt);
    	newContent.put(MSG_GEO_SPAN, geoSpan);
    	int categoriesNumber = msgCategories.length();
    	boolean[] bool_cat = new boolean[categoriesNumber];
    	for(int i=0;i<bool_cat.length;i++) {
    		if(msgCategories.charAt(i)=='1') {
				bool_cat[i] = true;
			}
    		else {
    			bool_cat[i] = false;
    		}
    		switch(i) {
    		case 0: {newContent.put(MSG_CATEGORY_GENERAL, bool_cat[i]); break;}
    		case 1: {newContent.put(MSG_CATEGORY_GAMES, bool_cat[i]); break;}
    		case 2: {newContent.put(MSG_CATEGORY_ADVERTISEMENTS, bool_cat[i]); break;}
    		case 3: {newContent.put(MSG_CATEGORY_SUGGESTIONS, bool_cat[i]); break;}
    		case 4: {newContent.put(MSG_CATEGORY_EVENTS, bool_cat[i]); break;}
    		case 5: {newContent.put(MSG_CATEGORY_6, bool_cat[i]); break;}
    		case 6: {newContent.put(MSG_CATEGORY_7, bool_cat[i]); break;}
    		case 7: {newContent.put(MSG_CATEGORY_8, bool_cat[i]); break;}
    		case 8: {newContent.put(MSG_CATEGORY_9, bool_cat[i]); break;}
    		case 9: {newContent.put(MSG_CATEGORY_10, bool_cat[i]); break;}
    		case 10: {newContent.put(MSG_CATEGORY_11, bool_cat[i]); break;}
    		case 11: {newContent.put(MSG_CATEGORY_12, bool_cat[i]); break;}
    		case 12: {newContent.put(MSG_CATEGORY_13, bool_cat[i]); break;}
    		case 13: {newContent.put(MSG_CATEGORY_14, bool_cat[i]); break;}
    		case 14: {newContent.put(MSG_CATEGORY_15, bool_cat[i]); break;}
    		case 15: {newContent.put(MSG_CATEGORY_16, bool_cat[i]); break;}
    		}
    	}
    	// TODO change creator ID with md5(userName, appKey)
    	newContent.put(MSG_CREATOR_ID, creatorId);
    	newContent.put(MSG_TITLE, title);
    	newContent.put(MSG_CONTENT, content);
    	newContent.put(MSG_EXTRA_TYPE, attachExt);
    	newContent.put(MSG_EXTRA_CONTENT, attachPath);
    	
    	newContent.put(MSG_HOP_COUNTER, hop_counter);
    	
    	newContent.put(MSG_DEL_ID, msg_del_id);
    	newContent.put(MSG_DEL_TIME, msg_del_time);
    	newContent.put(MSG_DEL_LAT, msg_del_lat);
    	newContent.put(MSG_DEL_LON, msg_del_lon);
    	newContent.put(MSG_DEL_ALT, msg_del_alt);
    	newContent.put(USER_ACCOUNT_ID, user_account_id);
    	
    	newContent.put(DEFERRED_SENDING, deferred_sending);
    	     	
    	CurrentUser.readCurrentUserSettings(context);
    	
    	// boolean readUnread, boolean msgDeleted, boolean synchNotsynch,
    	newContent.put(MSG_READ_UNREAD, readUnread);
    	newContent.put(MSG_DELETED, deletedNotdeleted);
    	newContent.put(SYNCH_NOTSYNCH, synchNotsynch);
    	newContent.put(UPDATED_NOTUPDATED, updatedNotupdated);
    	
    	newContent.put(LTC_TIME, ltcTime);
    	newContent.put(TC_NUMBER, tcNumber);
    	newContent.put(MSG_COPY_RATE, msgRate);
    	newContent.put(CREATOR_NICK_NAME, creatorNick);
    	newContent.put(MY_RATE, myRate);
    	
    	newContent.put(MSG_SIZE, msgSize);
    	
    	newContent.put(CRC_CHECK, crc);
    	
    	long msgIndex = db.insert(DATABASE_CONTENT_TABLE, null, newContent);
    	
    	// check quarantine space
        EpicContentManipulation cm = new EpicContentManipulation(context);
        cm.computeQuarantineSpace(msg_del_lat, msg_del_lon);
    	
    	String notificationBehavior = CurrentUser.getNotificationBehavior();
    	boolean alwaysNotify = false;
    	boolean neverNotify = false;
    	boolean catBanned = false;
    	boolean catMatch = false;
    	boolean geoSpanMatch = false;
    	boolean expirationMatch = false;
    	if(notificationBehavior.substring(0, 1).equals("1")) {
    		alwaysNotify = true;
    		String my_pref_cat = CurrentUser.getPrefCategories();
	    	for(int i=0; i<bool_cat.length; i++) {
	    		if(bool_cat[i]) {
	    			if(my_pref_cat.charAt(i)=='2') {catBanned = true; break;}
	    		}
	    	}
    	}
    	else {
    		alwaysNotify = false;
    		if(notificationBehavior.substring(1, 2).equals("1")) {neverNotify = true;}
    		if(notificationBehavior.substring(2, 3).equals("1")) {
		    	String my_pref_cat = CurrentUser.getPrefCategories();
		    	for(int i=0; i<bool_cat.length; i++) {
		    		if(bool_cat[i]) {
		    			if(my_pref_cat.charAt(i)=='1') {catMatch = true; break;}
		    			if(my_pref_cat.charAt(i)=='2') {catBanned = true; break;}
		    		}
		    	}
	    	}
	    	else { 
	    		catMatch = true; 
	    	}
    		if(notificationBehavior.substring(2, 3).equals("1")) {
    			android.location.Location myLocation = new android.location.Location("");
    	        myLocation.setLatitude(msg_del_lat);
    	        myLocation.setLongitude(msg_del_lon);
    	        android.location.Location msgLocation = new android.location.Location("");
    	        msgLocation.setLatitude(msgLat);
    	        msgLocation.setLongitude(msgLon);
    	        float distanceInMeters = myLocation.distanceTo(msgLocation);
    	    	
    	    	// CurrentUser.getGeoSpan has to return the value in [tens of meters]
    	    	if(CurrentUser.getGeoSpan() > distanceInMeters/10) { 
    	    		geoSpanMatch = true;
    	    	}
    		}
    		else { 
    			geoSpanMatch = true; 
    		}
	    	
    		if(notificationBehavior.substring(3, 4).equals("1")) {
    			// CurrentUser.getTimeSpan() has to return the value in [seconds]
    	    	if(CurrentUser.getTimeSpan() > expTime - msg_del_time) {  
    	    		expirationMatch = true;
    	    	}
    		}
    		else { 
    			expirationMatch = true; 
    		}
    	}
    	// Notify the message insertion: 
    	// IF (I'm not the creator and neverNotify==false and catBanned==false) 
    	// IF at least one msg category matches with user pref categories
    	// IF I'm closer than pref geo span with respect to the msg origin
    	// IF the message will expire before than CurrentUser.getTimeSpan()
    	if(!creatorId.equals(CurrentUser.getMd5ID(context)) && !neverNotify && !catBanned) {
    		if(alwaysNotify || (catMatch && geoSpanMatch && expirationMatch)) {
	    		String ns = Context.NOTIFICATION_SERVICE;
	    		if(mNotificationManager==null) {
	    			mNotificationManager = (NotificationManager) context.getSystemService(ns);
	    		}
	    		int icon = R.drawable.notification_36x36;
	    		CharSequence tickerText = "New Epic message";
	    		long notification_time = System.currentTimeMillis();
	    		CharSequence contentTitle = "Incoming Epic message";
	    		CharSequence contentText = "You received a new Epic message!";
	    		if(notificationIntent==null) {
	    			notificationIntent = new Intent(context, EpicMsgListTab.class);
	    			notificationIntent.putExtra("container", "cropUnread");
		    		contentIntent = PendingIntent.getActivity(context, 0, notificationIntent, 0);
	    		}
	    		if(notification==null) {
	    			notification = new Notification(icon, tickerText, notification_time);
	    		}
	    		String notificationType = CurrentUser.getNotificationType();
    			if(notificationType.charAt(0)=='1') {
    				notification.defaults |= Notification.DEFAULT_SOUND;
    			}
    			if(notificationType.charAt(1)=='1') {
    				notification.defaults |= Notification.DEFAULT_VIBRATE;
    			}
    			if(notificationType.charAt(2)=='1') {
    				notification.defaults |= Notification.DEFAULT_LIGHTS;
    			}
	    		notification.setLatestEventInfo(context, contentTitle, contentText, contentIntent);
	    		notification.flags = Notification.FLAG_AUTO_CANCEL;
	    		Cursor cursortmp = db.query(DATABASE_CONTENT_TABLE, null, 
	    				MSG_READ_UNREAD + " = '0'" + " AND " + getCropCategoriesCondition(), 
	    				null, null, null, null);
	    		int numberOfUnread = cursortmp.getCount();
	    		notification.number = numberOfUnread;
	    		mNotificationManager.notify(1, notification);
    		}
    	}
    	return msgIndex;
    }
    //####################################################################################################################
    
    public boolean markAsSent(String msg_id, Location location){
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(DEFERRED_SENDING)))!=0) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.DEFERRED_SENDING, false);
		    	cv.put(EpicDBAdapter.MSG_GEO_LAT, location.getLatitude());
		    	cv.put(EpicDBAdapter.MSG_GEO_LON, location.getLongitude());
		    	cv.put(EpicDBAdapter.MSG_GEO_ALT, location.getAltitude());
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
			return true;
    	}
    	else {
    		return false;
    	}
    }
    public boolean markAsNotSent(String msg_id){
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(DEFERRED_SENDING)))!=1) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.DEFERRED_SENDING, true);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
			return true;
    	}
    	else {
    		return false;
    	}
    }
    
    
    public boolean markAsSynched(String msg_id) {
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(SYNCH_NOTSYNCH)))!=1) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.SYNCH_NOTSYNCH, true);
		    	ContentValues cvUpdated = new ContentValues();
		    	cvUpdated.put(EpicDBAdapter.UPDATED_NOTUPDATED, true);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
		    	db.update(DATABASE_CONTENT_TABLE, cvUpdated, where, value);
			}
			return true;
    	}
    	else {
    		return false;
    	}
    }
    public boolean markAsNotSynched(String msg_id){
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(SYNCH_NOTSYNCH)))!=0) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.SYNCH_NOTSYNCH, false);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
			return true;
    	}
    	else {
    		return false;
    	}
    }
    
    
    public boolean markAsUpdated(String msg_id) {
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(UPDATED_NOTUPDATED)))!=1) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.UPDATED_NOTUPDATED, true);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
			return true;
    	}
    	else {
    		return false;
    	}
    }
    public boolean markAsNotUpdated(String msg_id) {
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(UPDATED_NOTUPDATED)))!=0) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.UPDATED_NOTUPDATED, false);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
			return true;
    	}
    	else {
    		return false;
    	}
    }
    public boolean setMyRating(String msg_id, double rate) {
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			ContentValues cv = new ContentValues();
			cv.put(EpicDBAdapter.MY_RATE, rate);
			String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
	    	String[] value = {msg_id}; // the value for the "where" clause.
	    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			return true;
    	}
    	else {
    		return false;
    	}
    }
    
    
    public void markAsRead(String msg_id) { 
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(MSG_READ_UNREAD)))!=1) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.MSG_READ_UNREAD, true);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
    	}
    }
    public void markAsUnread(String msg_id) { 
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(MSG_READ_UNREAD)))!=0) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.MSG_READ_UNREAD, false);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
			}
    	}
    }
    
    
    public int updateNumberOfTxCopies(String msg_id) {
    	int notc = 0;
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			notc = cursorTmp.getInt((cursorTmp.getColumnIndex(TC_NUMBER))) + 1;
			ContentValues cv = new ContentValues();
	    	cv.put(EpicDBAdapter.TC_NUMBER, notc);
			String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
	    	String[] value = {msg_id}; // the value for the "where" clause.
	    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
    	}
    	return notc;
    }
    
    
    public long updateLastTxCopyTime(String msg_id) {
    	long ltcTime = 0;
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			Date date = new Date();
			ltcTime = date.getTime()/1000;
			ContentValues cv = new ContentValues();
	    	cv.put(EpicDBAdapter.LTC_TIME, ltcTime);
			String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
	    	String[] value = {msg_id}; // the value for the "where" clause.
	    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
    	}
    	return ltcTime;
    }
    
    
    private void deleteRecursive(File fileOrDirectory) {
	    if (fileOrDirectory.isDirectory()) {
	        for (File child : fileOrDirectory.listFiles()) {
	            deleteRecursive(child);
	        }
	    }
	    fileOrDirectory.delete();
	}
    // TODO use and check this method (flag msg as deleted and remove its attachments)
    public boolean markAsDeleted(String msg_id) {     	
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + "='" + msg_id + "'", null, null, null, null);
    	if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			if(cursorTmp.getInt((cursorTmp.getColumnIndex(MSG_DELETED)))!=1) {
				ContentValues cv = new ContentValues();
		    	cv.put(EpicDBAdapter.MSG_DELETED, true);
				String where = EpicDBAdapter.MSG_ID + "=?"; // the "where" clause
		    	String[] value = {msg_id}; // the value for the "where" clause.
		    	db.update(DATABASE_CONTENT_TABLE, cv, where, value);
	    		// check if there are attachments
	    		String attachments_type_str = cursorTmp.getString(cursorTmp.getColumnIndex(MSG_EXTRA_TYPE));
				String attachments_path_str = cursorTmp.getString(cursorTmp.getColumnIndex(MSG_EXTRA_CONTENT));
				if(attachments_path_str!=null) {
					String dirStr = context.getExternalCacheDir() + "/" + msg_id;
					File dirTmp = new File(dirStr);
					deleteRecursive(dirTmp);
//					String state = Environment.getExternalStorageState();
//				    if(Environment.MEDIA_MOUNTED.equals(state)) {
//				    	if(removeAttachment(msg_id)){
//				    		Log.d("EpicDbAdapter", "removeContent: attachments deleted");
//				    	}
//				    	else {
//				    		Log.d("EpicDbAdapter", "removeContent: attachments not deleted");
//				    	}
//				    }
//				    else {
//				    	Log.d("EpicDBAdapter", "media not mounted: state=" + state);
//				    }
				}
				else {
					Log.d("EpicDBAdapter", "Epic message without attachments deleted");
				}
			}
			return true;
    	}
    	return false;
    }
    //####################################################################################################################
    // TODO use and check this method: query for msg in CROP, DROP or QUARANTINE   
    // if(MSG_CATEGORY_i + CATEGORY_i.inQuarantine == 3) {display in QUARANTINE}
    // if(MSG_CATEGORY_i + CATEGORY_i.inQuarantine == {0, 1, 2}) {display in CROP}
    
    public String getCropCategoriesCondition() {
    	CurrentUser.readCurrentUserSettings(context);
    	String prefCategories = CurrentUser.getPrefCategories();
    	int[] resCond = new int[16];
    	for(int i=0; i<16; i++) {
    		resCond[i] = (prefCategories.charAt(0)=='0' || prefCategories.charAt(0)=='1') ? 0 : 1;
    	}
    	String resultCondition = 
    			"(" + 
    					"(" + MSG_CATEGORY_GENERAL + "= '0' OR "+ MSG_CATEGORY_GENERAL + "> '" + resCond[0] + "')" +" AND " + 
    					"(" + MSG_CATEGORY_GAMES + "= '0' OR "+ MSG_CATEGORY_GAMES + "> '" + resCond[1] + "')" +" AND " +
    					"(" + MSG_CATEGORY_ADVERTISEMENTS + "= '0' OR "+ MSG_CATEGORY_ADVERTISEMENTS + "> '" + resCond[2] + "')" +" AND " +
    					"(" + MSG_CATEGORY_SUGGESTIONS + "= '0' OR "+ MSG_CATEGORY_SUGGESTIONS + "> '" + resCond[3] + "')" +" AND " +
    					"(" + MSG_CATEGORY_EVENTS + "= '0' OR "+ MSG_CATEGORY_EVENTS + "> '" + resCond[4] + "')" +" AND " +
    					"(" + MSG_CATEGORY_6 + "= '0' OR "+ MSG_CATEGORY_6 + "> '" + resCond[5] + "')" +" AND " +
    					"(" + MSG_CATEGORY_7 + "= '0' OR "+ MSG_CATEGORY_7 + "> '" + resCond[6] + "')" +" AND " +
    					"(" + MSG_CATEGORY_8 + "= '0' OR "+ MSG_CATEGORY_8 + "> '" + resCond[7] + "')" +" AND " +
    					"(" + MSG_CATEGORY_9 + "= '0' OR "+ MSG_CATEGORY_9 + "> '" + resCond[8] + "')" +" AND " +
    					"(" + MSG_CATEGORY_10 + "= '0' OR "+ MSG_CATEGORY_10 + "> '" + resCond[9] + "')" +" AND " +
    					"(" + MSG_CATEGORY_11 + "= '0' OR "+ MSG_CATEGORY_11 + "> '" + resCond[10] + "')" +" AND " +
    					"(" + MSG_CATEGORY_12 + "= '0' OR "+ MSG_CATEGORY_12 + "> '" + resCond[11] + "')" +" AND " +
    					"(" + MSG_CATEGORY_13 + "= '0' OR "+ MSG_CATEGORY_13 + "> '" + resCond[12] + "')" +" AND " +
    					"(" + MSG_CATEGORY_14 + "= '0' OR "+ MSG_CATEGORY_14 + "> '" + resCond[13] + "')" +" AND " +
    					"(" + MSG_CATEGORY_15 + "= '0' OR "+ MSG_CATEGORY_15 + "> '" + resCond[14] + "')" +" AND " +
    					"(" + MSG_CATEGORY_16 + "= '0' OR "+ MSG_CATEGORY_16 + "> '" + resCond[15] + "')"  + 
    			")"; 
    	return resultCondition;
    }
    
    public String getQuarantineCategoriesCondition() {
    	CurrentUser.readCurrentUserSettings(context);
    	String prefCategories = CurrentUser.getPrefCategories();
    	int[] resCond = new int[16];
    	for(int i=0; i<16; i++) {
    		resCond[i] = (prefCategories.charAt(0)=='0' || prefCategories.charAt(0)=='1') ? 0 : 1;
    	}
    	String resultCondition = 
    			"(" + 
    					"(" + MSG_CATEGORY_GENERAL + "= '1' AND "+ MSG_CATEGORY_GENERAL + "= '" + resCond[0] + "')" +" OR " + 
    					"(" + MSG_CATEGORY_GAMES + "= '1' AND "+ MSG_CATEGORY_GAMES + "= '" + resCond[1] + "')" +" OR " +
    					"(" + MSG_CATEGORY_ADVERTISEMENTS + "= '1' AND " + MSG_CATEGORY_ADVERTISEMENTS + "= '" + resCond[2] + "')" +" OR " +
    					"(" + MSG_CATEGORY_SUGGESTIONS + "= '1' AND " + MSG_CATEGORY_SUGGESTIONS + "= '" + resCond[3] + "')" +" OR " +
    					"(" + MSG_CATEGORY_EVENTS + "= '1' AND " + MSG_CATEGORY_EVENTS + "= '" + resCond[4] + "')" +" OR " +
    					"(" + MSG_CATEGORY_6 + "= '1' AND "+ MSG_CATEGORY_6 + "= '" + resCond[5] + "')" +" OR " +
    					"(" + MSG_CATEGORY_7 + "= '1' AND "+ MSG_CATEGORY_7 + "= '" + resCond[6] + "')" +" OR " +
    					"(" + MSG_CATEGORY_8 + "= '1' AND "+ MSG_CATEGORY_8 + "= '" + resCond[7] + "')" +" OR " +
    					"(" + MSG_CATEGORY_9 + "= '1' AND "+ MSG_CATEGORY_9 + "= '" + resCond[8] + "')" +" OR " +
    					"(" + MSG_CATEGORY_10 + "= '1' AND "+ MSG_CATEGORY_10 + "= '" + resCond[9] + "')" +" OR " +
    					"(" + MSG_CATEGORY_11 + "= '1' AND "+ MSG_CATEGORY_11 + "= '" + resCond[10] + "')" +" OR " +
    					"(" + MSG_CATEGORY_12 + "= '1' AND "+ MSG_CATEGORY_12 + "= '" + resCond[11] + "')" +" OR " +
    					"(" + MSG_CATEGORY_13 + "= '1' AND "+ MSG_CATEGORY_13 + "= '" + resCond[12] + "')" +" OR " +
    					"(" + MSG_CATEGORY_14 + "= '1' AND "+ MSG_CATEGORY_14 + "= '" + resCond[13] + "')" +" OR " +
    					"(" + MSG_CATEGORY_15 + "= '1' AND "+ MSG_CATEGORY_15 + "= '" + resCond[14] + "')" +" OR " +
    					"(" + MSG_CATEGORY_16 + "= '1' AND "+ MSG_CATEGORY_16 + "= '" + resCond[15] + "')" + 
    			")"; 
    	return resultCondition;
    }
    //####################################################################################################################
    
    public Cursor getUnreadContent() {
    	CurrentUser.readCurrentUserSettings(context);
    	Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
    			MSG_READ_UNREAD + "= '0'" + " AND " +
    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'", // TODO md5(user + key)
    			null, null, null, MSG_DEL_TIME + " DESC");
    	if (cursor != null) {
			cursor.moveToFirst();
		}
    	return cursor;
    }
    
    public Cursor getUnreadContents(String container) {
    	CurrentUser.readCurrentUserSettings(context);
    	if(container.equals("crop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
        			MSG_READ_UNREAD + "= '0'" + " AND " +  
        			getCropCategoriesCondition() + " AND " +
					MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " + // TODO md5(user + key)        	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'", // TODO md5(user + key)
        			null, null, null, MSG_DEL_TIME + " DESC");
        	if (cursor != null) {cursor.moveToFirst();}
        	return cursor;
    	}
    	else if(container.equals("drop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
        			MSG_READ_UNREAD + "= '0'" + " AND " +
        			MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " + // TODO md5(user + key)         	    			
        			MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'", // TODO md5(user + key)
        			null, null, null, MSG_DEL_TIME + " DESC");
        	if (cursor != null) {cursor.moveToFirst();}
        	return cursor;
    	}
    	else if(container.equals("quarantine")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
        			MSG_READ_UNREAD + "= '0'" + " AND " +  
        			getQuarantineCategoriesCondition() + " AND " +
					MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " + // TODO md5(user + key)         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'", // TODO md5(user + key)
        			null, null, null, MSG_DEL_TIME + " DESC");
        	if (cursor != null) {cursor.moveToFirst();}
        	return cursor;
    	}
    	else {Log.d("EpicDBAdapter", "call getUnreadContents with invalid string: " + container); return null;}
    }
	
    
    public Cursor getDeferredContents() {
    	CurrentUser.readCurrentUserSettings(context);
    	String str = DEFERRED_SENDING + "= '1'" + " AND " +
    			MSG_DELETED + " = '0'" + " AND " +
    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'";
    	Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
    			DEFERRED_SENDING + "= '1'" + " AND " +
    			MSG_DELETED + " = '0'" + " AND " +
    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'", // TODO md5(user + key)
    			null, null, null, MSG_DEL_TIME + " DESC");
    	if (cursor != null) {
			cursor.moveToFirst();
		}
    	return cursor;
    }
    
    
    // Method to remove the expired messages
    // when a message expires we remove the entry from the DB
    // if the message has attachments, delete them if the I'm not the creator
    // TODO delete attachments even if I'm the creator
    public boolean removeExpiredContents(long t_now) {
    	CurrentUser.readCurrentUserSettings(context);
    	int numDeleted_1 = 0;
    	int numDeleted_2 = 0;
    	Cursor cursorTmp;
//    	cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, 
//    			MSG_EXP_TIME + " < '" + t_now + "'" + " AND " +
//    			USER_ACCOUNT_ID + "='" + CurrentUser.getCurrentUsername() + "'" + " AND " + 
//    			MSG_CREATOR_ID + "!='" + CurrentUser.getCurrentUsername() + "'", 
//    			null, null, null, null);
    	cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, 
    			MSG_EXP_TIME + " < '" + t_now + "'" + " AND " +
    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'", 
    			null, null, null, null);
		if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			while(!cursorTmp.isAfterLast()) {
	    		// check if there are attachments
				String attachments_path_str = cursorTmp.getString(cursorTmp.getColumnIndex(MSG_EXTRA_CONTENT));
				if(attachments_path_str!=null) {
					// remove the attachments
					String msg_id = cursorTmp.getString(cursorTmp.getColumnIndex(MSG_ID));
					String dirStr = context.getExternalCacheDir() + "/" + msg_id;
					File dirTmp = new File(dirStr);
					deleteRecursive(dirTmp);
//					String state = Environment.getExternalStorageState();
//				    if(Environment.MEDIA_MOUNTED.equals(state)) {
//				    	String msg_id = cursorTmp.getString(cursorTmp.getColumnIndex(MSG_ID));
//				    	if(removeAttachment(msg_id)){
//				    		Log.d("EpicDbAdapter", "removeContent: attachments deleted");
//				    	}
//				    	else {
//				    		Log.d("EpicDbAdapter", "removeContent: attachments not deleted");
//				    	}
//				    }
//				    else { Log.d("EpicDBAdapter", "media not mounted: " + state); }
				}
				else { 
					Log.d("EpicDBAdapter", "Epic message without attachments"); 
				}
				cursorTmp.moveToNext();
			}
			numDeleted_1 = db.delete(DATABASE_CONTENT_TABLE, 
					MSG_EXP_TIME + " < '" + t_now + "'" + " AND " +
	    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
	    			MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'", 
					null);
		}
		cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, 
    			MSG_EXP_TIME + " < '" + t_now + "'" + " AND " +
    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
    			MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'", 
    			null, null, null, null);
		if (cursorTmp != null) {
			numDeleted_2 = db.delete(DATABASE_CONTENT_TABLE, 
					MSG_EXP_TIME + " < '" + t_now + "'" + " AND " +
	    			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
	    			MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'", 
					null);
		}
		if(numDeleted_1 >= 0 && numDeleted_2 >= 0) {
			return true;
		}
		else {
			return false;
		}
    }
    
    public boolean removeContent(String msg_id) {
    	CurrentUser.readCurrentUserSettings(context);
    	Cursor cursorTmp = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + " = '" + msg_id +"'", null, null, null, null);
    	int numDeleted = 0;
		if (cursorTmp != null) {
			cursorTmp.moveToFirst();
			String attachments_path_str = cursorTmp.getString(cursorTmp.getColumnIndex(MSG_EXTRA_CONTENT));
			if(attachments_path_str!=null) {
				// remove the attachments
				String dirStr = context.getExternalCacheDir() + "/" + msg_id;
				File dirTmp = new File(dirStr);
				deleteRecursive(dirTmp);
//				String state = Environment.getExternalStorageState();
//			    if(Environment.MEDIA_MOUNTED.equals(state)) {
//			    	if(removeAttachment(msg_id)){Log.d("EpicDbAdapter", "removeContent: attachments deleted");}
//			    	else {Log.d("EpicDbAdapter", "removeContent: attachments not deleted");}
//			    }
//			    else { Log.d("EpicDBAdapter", "media not mounted: " + state); }
			}
			else {Log.d("EpicDBAdapter", "Epic message without attachments");}
			numDeleted = db.delete(DATABASE_CONTENT_TABLE, MSG_ID + " = '" + msg_id +"'", null);
		}
		if(numDeleted == 1) {return true;}
		else {return false;}
    }
    
    // Method to retrieve the MSG_ID from the KEY_ID (integer primary key autoincrement)
    public Cursor getMsgId(long key_id) {
    	Cursor cursor = db.query(DATABASE_CONTENT_TABLE, 
    			new String[] {MSG_ID}, 
    			KEY_ID + " = '" + key_id + "'", null, null, null, null); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
    }
    // Method to retrieve the Epic message fields from the MSG_ID (only one) 
    public Cursor getContent(String msg_id) {
    	Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, MSG_ID + " = '" + msg_id +"'", null, null, null, null); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
    }
    // Method to retrieve the Epic message fields from the MSG_IDs (more then one) 
    public Cursor getContents(String[] ids) {    			
    	
    	int ii;
    	String WHERE = " WHERE ";
    	String sql = " SELECT *, ( SELECT ( ";
    	for(int i=0; i<ids.length-1; i++) {
    		ii = i+1;
    		WHERE = WHERE + MSG_ID + " = '" + ids[i] + "' OR ";
    		sql = sql + "(CASE WHEN " + MSG_ID + " = '" + ids[i] + "' THEN " + ii + " ELSE  0  END) + ";
    	}
    	ii = ids.length;
    	WHERE = WHERE + MSG_ID + " = '" + ids[ids.length-1] + "'";
    	sql = sql + "(CASE WHEN " + MSG_ID + " = '" + ids[ids.length-1] + "' THEN " + ii + " ELSE  0  END) ";
    	sql = sql + ")) AS " + PROXIMITY + " FROM " + DATABASE_CONTENT_TABLE;
    	sql = sql + WHERE + " ORDER BY " + PROXIMITY;
		Cursor cursor = db.rawQuery(sql,  null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
    }
    
    // return a Cursor over the list of all items in DATABASE_CONTENT_TABLE
	public Cursor getAllContents() {
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE,null, null, null, null, null, null); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	// return all contents (with all columns) COLLECTED or CREATED by this specific user account id
	public Cursor getAllContentsByLoggedUser() {
		CurrentUser.readCurrentUserSettings(context);
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'",
				null, null, null, null); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	// method used to show the messages created by users different from the current one
	// (N.B.: if I have 2 accounts (A and B) and I'm logged with A 
	// and I receive from user C a message created by B, it should be as not created by me) 
	public Cursor getFilteredContentsByLoggedUsers() {
		CurrentUser.readCurrentUserSettings(context);
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
				MSG_DELETED + " = '0'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " +
				MSG_CREATOR_ID + " != '" + CurrentUser.getMd5ID(context) + "'", null, null, null,  MSG_DEL_TIME + " DESC");
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	// method used to show the messages created by the current user
	public Cursor getContentsCreatedByLoggedUsers() {
		CurrentUser.readCurrentUserSettings(context);
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,
				MSG_DELETED + " = '0'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
				MSG_CREATOR_ID + " = '" + CurrentUser.getMd5ID(context) + "'", null, null, null, MSG_DEL_TIME + " DESC");
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	// TODO use and check this method: query for msg in CROP, DROP or QUARANTINE   
	public Cursor getContents(String container) {
		CurrentUser.readCurrentUserSettings(context);
    	String prefCategories = CurrentUser.getPrefCategories();
    	if(container.equals("crop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
    				getCropCategoriesCondition() + " AND " +
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_DEL_TIME + " DESC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else if(container.equals("drop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
        			MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_DEL_TIME + " DESC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else if(container.equals("quarantine")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,  
    				getQuarantineCategoriesCondition() + " AND " +
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_DEL_TIME + " DESC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else {
    		Log.d("EpicDBAdapter", "call getContents with invalid string: " + container);
    		return null;
    	}
	}
	
	
	// return all not synched contents
	public Cursor getNotSynchContents(){
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, 
				null, 
				SYNCH_NOTSYNCH + " = '0'" + " AND " + MSG_DELETED + " = '0'",
				null, null, null, null); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	// return all not updated contents
	public Cursor getNotUpdatedContents(){
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, 
				null, 
				UPDATED_NOTUPDATED + " = '0'" + " AND " + MSG_DELETED + " = '0'",
				null, null, null, null); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	
	// method used to get the contents to propose
	// we don't propose to your_user_id messages created or delivered by him/her  
	public Cursor getFilteredContentsByUsers(String your_user_id) {
		CurrentUser.readCurrentUserSettings(context);
		Date date = new Date();
		long tNow = date.getTime()/1000;
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE,null,
				MSG_DELETED + " = '0'" + " AND " +
				DEFERRED_SENDING + " = '0'"  + " AND " +
				MSG_EXP_TIME + " > '" + tNow + "'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
				MSG_DEL_ID + " != '" + your_user_id + "'" + " AND " +  
				MSG_CREATOR_ID + " != '" + your_user_id + "'", null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	private static final String NUMBER_OF_MATCH = "number_of_match";
	public Cursor getContentsOrderedByMatchingCategories(String container) {		
		String tmp = null;
		if(container.equals("crop")) {tmp = MSG_CREATOR_ID + "!= '" + CurrentUser.getMd5ID(context) + "' AND " + getCropCategoriesCondition();}
		else if(container.equals("drop")) {tmp = MSG_CREATOR_ID + "= '" + CurrentUser.getMd5ID(context) + "'";}
		else if(container.equals("quarantine")) {tmp = MSG_CREATOR_ID + "!= '" + CurrentUser.getMd5ID(context) + "' AND " + getQuarantineCategoriesCondition();}
		CurrentUser.readCurrentUserSettings(context);
		String my_pref_categories = CurrentUser.getPrefCategories();
		String[] m_p_c = new String[my_pref_categories.length()];
		for(int i=0; i<my_pref_categories.length(); i++) {
			m_p_c[i] = String.valueOf(my_pref_categories.charAt(i));
		}
		String sql = "SELECT *, ( SELECT ( " +
				"(CASE WHEN " + MSG_CATEGORY_GENERAL + " = " + m_p_c[0] + " THEN 1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_GAMES + " = " + m_p_c[1] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_ADVERTISEMENTS + " = " + m_p_c[2] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_SUGGESTIONS + " = " + m_p_c[3] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_EVENTS + " = " + m_p_c[4] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_6 + " = " + m_p_c[5] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_7 + " = " + m_p_c[6] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_8 + " = " + m_p_c[7] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_9 + " = " + m_p_c[8] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_10 + " = " + m_p_c[9] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_11 + " = " + m_p_c[10] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_12 + " = " + m_p_c[11] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_13 + " = " + m_p_c[12] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_14 + " = " + m_p_c[13] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_15 + " = " + m_p_c[14] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_16 + " = " + m_p_c[15] + " THEN   1 ELSE  0  END)" +
				")) AS " + NUMBER_OF_MATCH + " FROM " + DATABASE_CONTENT_TABLE + 			
				" WHERE " + MSG_DELETED + " = '0'" + " AND " + 
				USER_ACCOUNT_ID + "= '" + CurrentUser.getMd5ID(context) + "'" + " AND " +
				tmp +
				" ORDER BY " + NUMBER_OF_MATCH;
		Cursor cursor = db.rawQuery(sql,  null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public Cursor getContentOrderedByMatchingCategories() {
		CurrentUser.readCurrentUserSettings(context);
		String my_pref_categories = CurrentUser.getPrefCategories();
		String[] m_p_c = new String[my_pref_categories.length()];
		for(int i=0; i<my_pref_categories.length(); i++) {
			m_p_c[i] = String.valueOf(my_pref_categories.charAt(i));
		}
		String sql = "SELECT *, ( SELECT ( " +
				"(CASE WHEN " + MSG_CATEGORY_GENERAL + " = " + m_p_c[0] + " THEN 1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_GAMES + " = " + m_p_c[1] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_ADVERTISEMENTS + " = " + m_p_c[2] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_SUGGESTIONS + " = " + m_p_c[3] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_EVENTS + " = " + m_p_c[4] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_6 + " = " + m_p_c[5] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_7 + " = " + m_p_c[6] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_8 + " = " + m_p_c[7] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_9 + " = " + m_p_c[8] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_10 + " = " + m_p_c[9] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_11 + " = " + m_p_c[10] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_12 + " = " + m_p_c[11] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_13 + " = " + m_p_c[12] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_14 + " = " + m_p_c[13] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_15 + " = " + m_p_c[14] + " THEN   1 ELSE  0  END) + " +
				"(CASE WHEN " + MSG_CATEGORY_16 + " = " + m_p_c[15] + " THEN   1 ELSE  0  END)" +
				")) AS " + NUMBER_OF_MATCH + " FROM " + DATABASE_CONTENT_TABLE + 			
				" WHERE " + MSG_DELETED + " = '0'" + " AND " + 
				USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "' AND " +
				MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'" +
				" ORDER BY " + NUMBER_OF_MATCH;
		Cursor cursor = db.rawQuery(sql,  null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	
	private static final String PROXIMITY = "proximity";
	public Cursor getContentOrderedByProximity(double lat, double lon) {
		CurrentUser.readCurrentUserSettings(context);
		String sql = "SELECT *" +", ( SELECT "+
				"(" + MSG_GEO_LAT + "-'"+ lat + "')*"+"(" + MSG_GEO_LAT + "-'"+ lat + "')"+ 
				"+" + 
				"(" + MSG_GEO_LON + "-'"+ lon + "')*"+"(" + MSG_GEO_LON + "-'"+ lon + "')"+") AS " +
				PROXIMITY + " FROM " + DATABASE_CONTENT_TABLE + 
				" WHERE " + MSG_DELETED + " = '0'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
				MSG_CREATOR_ID + " != '" + CurrentUser.getMd5ID(context) + "'" +
				" ORDER BY " + PROXIMITY  + " ASC";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
    }
	// class used to define couples of {msgId, ||currentPos - msgIdPos||} values and to compare them based on the distance field  
	private class MsgIdDistanceItem implements Comparable {
		private String msgId;
		private float distance;
		public MsgIdDistanceItem() {
			this.msgId = "";
			this.distance = 0;
		}
		public void setMsgId(String msgId) {
			this.msgId = msgId;
		}
		public String getMsgId() {
			return msgId;
		}
		public void setDistance(float distance) {
			this.distance = distance;
		}
		public double getDistance() {
			return distance;
		}
		@Override
		public int compareTo(Object another) {
			MsgIdDistanceItem tmp = (MsgIdDistanceItem) another;
			if(this.getDistance()<tmp.getDistance()){return -1;}
			else if(this.getDistance()>tmp.getDistance()){return 1;}
			else{return 0;}
		}
		
	}
	// This method should return an array of msg IDs ordered based on the distance between message origin and current position
	// TODO check the correctness of this method
	public String[] getMsgIdOrderedByProximity(double lat, double lon, String container) {
		String[] result = null;
		String tmp = null;
		if(container.equals("crop")) {tmp = MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "' AND " + getCropCategoriesCondition();}
		else if(container.equals("drop")) {tmp = MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'";}
		else if(container.equals("quarantine")) {tmp = MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "' AND " + getQuarantineCategoriesCondition();}
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
				MSG_DELETED + " = '0'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " + tmp, null, null, null, null);
		if (cursor != null) {
			int cursorSize = cursor.getCount();
			result = new String[cursorSize];
			MsgIdDistanceItem[] tmpArray = new MsgIdDistanceItem[cursorSize];
			double latTmp;
			double lonTmp;
			cursor.moveToFirst();
			int i = 0;
			while(!cursor.isAfterLast()) {
				latTmp = cursor.getDouble(cursor.getColumnIndex(MSG_GEO_LAT));
				lonTmp = cursor.getDouble(cursor.getColumnIndex(MSG_GEO_LON));
				android.location.Location myLocation = new android.location.Location("");
			    myLocation.setLatitude(lat);
			    myLocation.setLongitude(lon);
			    android.location.Location msgLocation = new android.location.Location("");
			    msgLocation.setLatitude(latTmp);
			    msgLocation.setLongitude(lonTmp);
			    float distanceInMeters = myLocation.distanceTo(msgLocation);
			    tmpArray[i] = new MsgIdDistanceItem();
	    		tmpArray[i].setDistance(distanceInMeters);
	    		tmpArray[i].setMsgId(cursor.getString(cursor.getColumnIndex(MSG_ID)));
	    		i++;
				cursor.moveToNext();
			}
			Arrays.sort(tmpArray);
			for(i=0; i<cursorSize; i++) {
				result[i] = tmpArray[i].getMsgId();
			}
		}
		return result;
	}
	
	
	public Cursor getContentOrderedByExpireTime() {
		CurrentUser.readCurrentUserSettings(context);
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE,null, 
				MSG_DELETED + " = '0'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " + 
				MSG_CREATOR_ID + " != '" + CurrentUser.getMd5ID(context) + "'", 
				null, null, null, MSG_EXP_TIME + " ASC"); 
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
    }
	// TODO use and check this method: query for msg in CROP, DROP or QUARANTINE   
	public Cursor getContentsOrderedByExpirationTime(String container)  {
		CurrentUser.readCurrentUserSettings(context);
    	if(container.equals("crop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
    				getCropCategoriesCondition() + " AND " +
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_EXP_TIME + " ASC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else if(container.equals("drop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
        			MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_EXP_TIME + " ASC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else if(container.equals("quarantine")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,  
    				getQuarantineCategoriesCondition() + " AND " +
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_EXP_TIME + " ASC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else {
    		Log.d("EpicDBAdapter", "call getContentsOrderedByExpirationTime with invalid string: " + container);
    		return null;
    	}
	}
	
	
	// return both read and unread contents grouped by read and unread and sorted by delivery time 
	public Cursor getContentOrderedByReadUnread() {
		CurrentUser.readCurrentUserSettings(context);
		Cursor cursor = db.query(DATABASE_CONTENT_TABLE,null, 
				MSG_DELETED + " = '0'" + " AND " +
				USER_ACCOUNT_ID + " = '" + CurrentUser.getMd5ID(context) + "'" + " AND " +  
				MSG_CREATOR_ID + " != '" + CurrentUser.getMd5ID(context) + "'", 
				null, null, null,
				MSG_READ_UNREAD + " ASC, " + MSG_DEL_TIME + " ASC");
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	// TODO use and check this method: query for msg in CROP, DROP or QUARANTINE   
	public Cursor getContentsOrderedByReadUnread(String container) {
		CurrentUser.readCurrentUserSettings(context);
    	if(container.equals("crop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null, 
    				getCropCategoriesCondition() + " AND " +
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_READ_UNREAD + " ASC, " + MSG_DEL_TIME + " ASC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else if(container.equals("drop")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
        			MSG_CREATOR_ID + "='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_READ_UNREAD + " ASC, " + MSG_DEL_TIME + " ASC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else if(container.equals("quarantine")) {
    		Cursor cursor = db.query(DATABASE_CONTENT_TABLE, null,  
    				getQuarantineCategoriesCondition() + " AND " +
    				MSG_DELETED + " = '0'" + " AND " +
        			USER_ACCOUNT_ID + "='" + CurrentUser.getMd5ID(context) + "'" + " AND " +         	    			
                	MSG_CREATOR_ID + "!='" + CurrentUser.getMd5ID(context) + "'",
        			null, null, null, MSG_READ_UNREAD + " ASC, " + MSG_DEL_TIME + " ASC");
        	if (cursor != null) {
    			cursor.moveToFirst();
    		}
        	return cursor;
    	}
    	else {
    		Log.d("EpicDBAdapter", "call getContentsOrderedByReadUnread with invalid string: " + container);
    		return null;
    	}
	}

    //####################################################################################################
    //################################################################################
    // Method used to build the content packet (Epic message and attachments if any) #
    //################################################################################
	public byte[] getContentByte(String msgId, Context c) {
		Cursor cursor = getContent(msgId);
		byte[] byteStreamFixedLength = new byte[CONTENT_PACKET_FIXED_LENGTH];
    	int version = EpicVersionSettings.commVersion;
    	int type = EpicVersionSettings.contentType * 16;
    	char blank = ' '; 
    	byteStreamFixedLength[0] = (byte) (version + type);
    	//msg_id 
    	String msg_id = cursor.getString(cursor.getColumnIndex(MSG_ID));
    	String mac_first_part = msg_id.substring(0, 2);
		String mac_second_part = msg_id.substring(2,4);
		byteStreamFixedLength[1] = stringToHex(mac_first_part);
		byteStreamFixedLength[2] = stringToHex(mac_second_part);
		byte[] time_tag_bytes = intToByteArray(Integer.parseInt(msg_id.substring(4)));
		byteStreamFixedLength[3] = time_tag_bytes[0];
		byteStreamFixedLength[4] = time_tag_bytes[1];
		byteStreamFixedLength[5] = time_tag_bytes[2];
		byteStreamFixedLength[6] = time_tag_bytes[3];
		//msg_expired_time
		int msg_expired_time = cursor.getInt(cursor.getColumnIndex(MSG_EXP_TIME));
		byte[] expired_time_tag_bytes = intToByteArray(msg_expired_time);
		byteStreamFixedLength[7] = expired_time_tag_bytes[0];
		byteStreamFixedLength[8] = expired_time_tag_bytes[1];
		byteStreamFixedLength[9] = expired_time_tag_bytes[2];
		byteStreamFixedLength[10] = expired_time_tag_bytes[3];
		//lat, long, alt
		double coordinate = cursor.getDouble(cursor.getColumnIndex(MSG_GEO_LAT));
		byte[] location_byte_array = doubleToByteArray(coordinate);
		for(int i = 0; i<8;i++){
			byteStreamFixedLength[i+11] = location_byte_array[i];
		}
		coordinate = cursor.getDouble(cursor.getColumnIndex(MSG_GEO_LON));
		location_byte_array = doubleToByteArray(coordinate);
		for(int i = 0; i<8;i++){
			byteStreamFixedLength[i+19] = location_byte_array[i];
		}
		coordinate = cursor.getDouble(cursor.getColumnIndex(MSG_GEO_ALT));
		location_byte_array = doubleToByteArray(coordinate);
		for(int i = 0; i<8;i++){
			byteStreamFixedLength[i+27] = location_byte_array[i];
		}
		//geo_span
		int msg_geo_span = cursor.getInt(cursor.getColumnIndex(MSG_GEO_SPAN));
		byte[] msg_geo_span_array = intToDualByteArray(msg_geo_span);
		byteStreamFixedLength[35] = msg_geo_span_array[0];
		byteStreamFixedLength[36] = msg_geo_span_array[1];
		//msg_categories
		int tmp;
		String msg_cat = "";
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_GENERAL));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_GAMES));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_ADVERTISEMENTS));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_SUGGESTIONS));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_EVENTS));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_6));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_7));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_8));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_9));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_10));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_11));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_12));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_13));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_14));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_15));
		msg_cat = msg_cat + cursor.getInt(cursor.getColumnIndex(MSG_CATEGORY_16));
    	int cat_val_1 = 0;
    	int cat_val_2 = 0;
    	for(int i = 0; i< 8; i++){
    		if(msg_cat.charAt(i) == '1') {
    			cat_val_1 = cat_val_1 + (int) Math.pow(2, 8-i-1);
    		}
    	}
    	for(int i = 8; i< 16; i++){
    		if(msg_cat.charAt(i) == '1') {
    			cat_val_2 = cat_val_2 + (int) Math.pow(2, 16-i-1);
    		}
    	}
    	byteStreamFixedLength[37] = (byte) cat_val_1;
    	byteStreamFixedLength[38] = (byte) cat_val_2;
    	// creator account id
    	String user_account_id = cursor.getString(cursor.getColumnIndex(MSG_CREATOR_ID));
    	int user_length = user_account_id.length();
    	for(int i = 0; i < 16; i++){
    		if(i < user_length){
    			byte a = (byte) user_account_id.charAt(i);
    			byteStreamFixedLength[i+39] = a;
    		}else{		
    			byte a = (byte) blank;
    			byteStreamFixedLength[i+39] = a;
    		}
    	}
    	// creator nick name
    	user_account_id = cursor.getString(cursor.getColumnIndex(CREATOR_NICK_NAME));
    	user_length = user_account_id.length();
    	for(int i = 0; i < 16; i++){
    		if(i < user_length){
    			byte a = (byte) user_account_id.charAt(i);
    			byteStreamFixedLength[i+55] = a;
    		}else{		
    			byte a = (byte) blank;
    			byteStreamFixedLength[i+55] = a;
    		}
    	}
    	// hop_number
    	int msg_hop_number = cursor.getInt(cursor.getColumnIndex(MSG_HOP_COUNTER));
    	byte[] msg_hop_number_array = intToDualByteArray(msg_hop_number);
    	byteStreamFixedLength[71] = msg_hop_number_array[0];
    	byteStreamFixedLength[72] = msg_hop_number_array[1];
    	// approximate msg size
    	int msg_size = cursor.getInt(cursor.getColumnIndex(MSG_SIZE));
    	byte[] msg_size_array = intToByteArray(msg_size);
    	byteStreamFixedLength[73] = msg_size_array[0];
    	byteStreamFixedLength[74] = msg_size_array[1];
    	byteStreamFixedLength[75] = msg_size_array[2];
    	byteStreamFixedLength[76] = msg_size_array[3];
    	// msg rate
    	double myRate = cursor.getDouble(cursor.getColumnIndex(MY_RATE));
    	double rxRate = cursor.getInt(cursor.getColumnIndex(MSG_COPY_RATE));
    	byte[] msgRate_array;
    	if(msg_hop_number==0) {
    		msgRate_array = doubleToByteArray(rxRate);
    	}
    	else {
    		msgRate_array = doubleToByteArray((rxRate*msg_hop_number + myRate)/(msg_hop_number+1));
    	}
		for(int i = 0; i<8;i++) {
			byteStreamFixedLength[i+77] = msgRate_array[i];
		}
    	// msg md5 crc 
		// TODO
    	// CRC(MSG_ID, TIME(expiration), GEO(x,y,z,span), CATEGORIES, CREATOR_ID, TITLE, CONTENT, attachments byte stream)
    	long crcCheck = cursor.getLong(cursor.getColumnIndex(CRC_CHECK));
    	byte[] crcByteArray = longToByteArray(crcCheck);
    	int crc_length = crcByteArray.length;
    	for(int i = 0; i < crc_length; i++) {
    		byteStreamFixedLength[i+85] = crcByteArray[i];
    	}
		// number of attachments
    	int noa;
		String attachments_extension_str = cursor.getString(cursor.getColumnIndex(MSG_EXTRA_TYPE));
		String attachments_path_str = cursor.getString(cursor.getColumnIndex(MSG_EXTRA_CONTENT));
		String[] attachments_extension_array = null;
		String[] attachments_path_array = null;
		if(attachments_extension_str == null ) {
			noa = 0;
			byteStreamFixedLength[93] = (byte) noa;
	    }
	    else {
	    	attachments_extension_array = attachments_extension_str.split(",");
	    	attachments_path_array = attachments_path_str.split(",");
	    	noa = attachments_extension_array.length;
	    	byteStreamFixedLength[93] = (byte) attachments_extension_array.length;
	    }
		// title length
		String title = (cursor.getString(cursor.getColumnIndex(MSG_TITLE)));
		byte[] byteStreamTitle = title.getBytes();
		int titleLength = byteStreamTitle.length;
		if(titleLength > Math.pow(2, 8)-1) {
			Log.w("EpicDBAdapter", "Attention!! Title length = " + titleLength);
		}
		byteStreamFixedLength[94] = (byte) titleLength;
		// content length
		String content = (cursor.getString(cursor.getColumnIndex(MSG_CONTENT)));
		byte[] byteStreamContent = content.getBytes();
		int contentLength = content.length();
		if(contentLength > Math.pow(2, 16)-1) {
			Log.w("EpicDBAdapter", "Attention!! Content length = " + contentLength);
		}
		byte[] content_length_array = intToDualByteArray(contentLength);
		byteStreamFixedLength[95] = content_length_array[0];
		byteStreamFixedLength[96] = content_length_array[1];
		
		// compute VARIABLE_LENGTH
		long[] attach_length_array = null;
		String[] attach_fname_array = null;
		int[] attach_fnameLength_array = null;
		long total_file_length = 0;
		if(noa>0) {
			attach_length_array = new long[noa];
			attach_fname_array = new String[noa];
			attach_fnameLength_array = new int[noa];
			int attachIndex = 0;
			for(String singlePath : attachments_path_array) {
				long filesize = getFileSize(singlePath);
				attach_length_array[attachIndex] = filesize;
				attach_fname_array[attachIndex] = (singlePath.split("/")[singlePath.split("/").length-1]);
				attach_fnameLength_array[attachIndex] = (attach_fname_array[attachIndex].getBytes()).length;
				if(attach_fnameLength_array[attachIndex] > 255) {
					Log.w("EpicDBAdapter", "Attention!! File name too long = " + attach_fnameLength_array[attachIndex]);
				}
				total_file_length = total_file_length + filesize + attach_fnameLength_array[attachIndex];
				attachIndex++;
			}
		}
		long attachmentsLength = 5 * noa + // filename length field (1 byte) + type field (1 byte) + attachments length fields (3 byte) 
								total_file_length;
		byte[] byteStreamVariableLength;
		byteStreamVariableLength = new byte[(int)(titleLength + contentLength + attachmentsLength)];
		int current_offset = 0;
		// msg title
		System.arraycopy(byteStreamTitle, 0, 
				byteStreamVariableLength, current_offset, 
				titleLength);
		current_offset = current_offset + titleLength;
		// msg content
		System.arraycopy(byteStreamContent, 0, 
				byteStreamVariableLength, current_offset, 
				contentLength);
		current_offset = current_offset + contentLength;
		if(noa > 0) {
			int attachIndex = 0;
			for(String singlePath : attachments_path_array ) {
				// attachment file name length
				byteStreamVariableLength[(int) current_offset] = (byte) attach_fnameLength_array[attachIndex];
				current_offset++;
				
				// attachment file name
				System.arraycopy((attach_fname_array[attachIndex].getBytes()), 0, 
						byteStreamVariableLength, current_offset, 
						attach_fnameLength_array[attachIndex]);
				current_offset = current_offset + attach_fnameLength_array[attachIndex];
				
				// attachment extension
				byteStreamVariableLength[(int) current_offset] = (byte) resolveAttachmentType(attachments_extension_array[attachIndex]);
				current_offset++;
				
				byte[] att_length_bytes_array = longToTripleByteArray(attach_length_array[attachIndex]);
				// attachment file size
				System.arraycopy(att_length_bytes_array, 0, 
						byteStreamVariableLength, current_offset, 
						att_length_bytes_array.length);
				current_offset = current_offset + att_length_bytes_array.length;
				
				byte[] attachment = getByteArrayFromFile(c,singlePath);
				// attachment file byte stream
				System.arraycopy(attachment, 0, 
						byteStreamVariableLength, current_offset, 
						(int) attach_length_array[attachIndex]);
				current_offset = (int) (current_offset + attach_length_array[attachIndex]);
				attachIndex++;
			}
		}
		byte[] b_b_var = new byte[byteStreamFixedLength.length + byteStreamVariableLength.length];
		System.arraycopy(byteStreamFixedLength, 0, b_b_var, 0, byteStreamFixedLength.length);
		System.arraycopy(byteStreamVariableLength, 0, b_b_var, byteStreamFixedLength.length, byteStreamVariableLength.length);
		return b_b_var;
	}
	
	//####################################################################################################
	//####################
	//conversion methods #
	//####################
	public byte stringToHex(String arg) {
	     byte b = (byte) Integer.parseInt(arg, 16);
	     return b;
	}
	
	public byte[] doubleToByteArray(double mydouble) {
		long value = Double.doubleToLongBits(mydouble);
		return new byte[] {(byte)(value >> 56),
				 			(byte)(value >> 48),
				 			(byte)(value >> 40),
				 			(byte)(value >> 32),
						 	(byte)(value >> 24),
						 	(byte)(value >> 16),
						 	(byte)(value >> 8),
						 	(byte)value};
	}
	public byte[] longToByteArray(long value){
		return new byte[] {(byte)(value >> 56),
					 		(byte)(value >> 48),
					 		(byte)(value >> 40),
					 		(byte)(value >> 32),
							(byte)(value >> 24),
							(byte)(value >> 16),
							(byte)(value >> 8),
							(byte)value};
	}
	public byte[] intToByteArray(int value) {
		return new byte[] {
				(byte)(value >> 24),
				(byte)(value >> 16),
				(byte)(value >> 8),
				(byte)value};
   	}
   	public byte[] longToDualByteArray(long value) {
   		if(value>(Math.pow(2, 16)-1)) {
   			Log.d("EpicDBAdapter", "longToDualByteArray(.): not enough bytes to represent value=" + value);
   		}
   		else if(value<0) {
   			Log.w("EpicDBAdapter", "longToDualByteArray(.): negative value=" + value);
   		}
   		value = (int)value;
   		return new byte[] {
   				(byte)(value >> 8),
   				(byte)value };
   	}
   	public byte[] intToDualByteArray(int value) {
   		if(value>(Math.pow(2, 16)-1)) {
   			Log.d("EpicDBAdapter", "intToDualByteArray(.): not enough bytes to represent value=" + value);
   		}
   		else if(value<0) {
   			Log.w("EpicDBAdapter", "intToDualByteArray(.): negative value=" + value);
   		}
   		return new byte[] {
   				(byte)(value >> 8),
   				(byte)value};
   	}
   	public byte[] longToTripleByteArray(long value){
   		if(value>(Math.pow(2, 24)-1)) {
   			Log.d("EpicDBAdapter", "longToTripleByteArray(.): not enough bytes to represent value=" + value);
   		}
   		else if(value<0) {
   			Log.w("EpicDBAdapter", "longToTripleByteArray(.): negative value=" + value);
   		}
   		return new byte[] {(byte) (value >> 16),
   				(byte)(value >> 8),
   				(byte)value };	   
   	}
   	public  int byteArrayToInt(byte [] b) {
   		return ((b[0] & 0xff) << 24)
   				+ ((b[1] & 0xff) << 16)
   				+ ((b[2] & 0xff) << 8)
   				+ (b[3] & 0xff);
   	}
   //############################################################################################
   	public long getFileSize(String path){
   		// return the size in bytes of the file located in path
    	long filesize = 0; 
 	    try {
 		    File fileTmp = new File(path);
 		    filesize = fileTmp.length();
 	    }
 	    catch (Exception e) {
 		    if(e.toString() == "FileNotFoundException"){
 		    	e.printStackTrace();
 			    return -1;
 		    }
 		    else if(e.toString() == "IOException"){
 			    e.printStackTrace();
 			    return -2;
 		    }
 		    else {
 		    	Log.e("EpicDBAdapter", "exception catched in getFileSize(.): " + e.toString());
 		    }
 	    }
 	    return filesize;
 	}
   
   	//############################################################################################
   	public byte[] getByteArrayFromFile(Context c, String path){
   		try {
   			File fileTmp = new File(path);
   			long fileSize = fileTmp.length();
   			byte[] buffer = new byte[(int) fileSize];
   			FileInputStream fis = new FileInputStream(fileTmp);
   			int i=0;
   			while(i<fileSize) {
   				try {
   					i=fis.read(buffer, 0, (int) fileSize);
   				} catch (IOException e) {
   					e.printStackTrace();
   				}	
   			}
   			fis.close();
   			return buffer;
   		} 
   		catch (Exception e) {
   			//return the array of bytes corresponding to the URI passed as method's argument
   			if(e.toString() == "FileNotFoundException") {
   				Log.d("FILE NOT FOUND", "getByteArrayFromFile Exception");
				e.printStackTrace();
				return null;
   			} 
   			else if(e.toString() == "IOException") {
				Log.d("IO EXCEPTION", "getByteArrayFromFile Exception");
				e.printStackTrace();
				return null;
			}
		}
	    return null;
    }
   
    public String resolveAttachmentType(int extensionIndex){
    	if(extensionIndex>=0) {
    		return FileExtensionMap.getSingelton().getExtensionFromIndex(extensionIndex);
 	   	}
 	   	else {
 		   	return null;
 	   	}
    }
    public int resolveAttachmentType(String fileExtension){
    	if(FileExtensionMap.getSingelton().hasExtension(fileExtension)) {
    		return FileExtensionMap.getSingelton().getIndexFromExtension(fileExtension);
    	}
    	else {
    		return -1;
    	}
    }
   
    
    public String addAttachment(String msg_id, String filename, String file_extension, byte[] arr_content) {
    	File cachePath = context.getExternalCacheDir();
    	File msgIdDir = new File(cachePath + "/" + msg_id);
    	if(!msgIdDir.exists()) {
    		msgIdDir.mkdirs();
    	}
    	String filePath = cachePath + "/" + msg_id + "/" + filename;
    	Log.d("EpicDBAdapter", "addAttachment: filePath = " + filePath);
   	    File newFile = new File(filePath);
	    try {
			FileOutputStream content_writer = new FileOutputStream(newFile);
			content_writer.write(arr_content);
			content_writer.close();
		} 
	    catch (FileNotFoundException e) {
			Log.d("ADD ATTACHMENT", "Error - File doesn't found exception");
			e.printStackTrace();
			return null;
		} 
	    catch (IOException e) {
			Log.d("ADD ATTACHMENT method: addAttachment()", "Error in file writing operation");
			e.printStackTrace();
			return null;
		}
	    return filePath;
   }
   
//   //TODO test this feature
//   public boolean removeAttachment(String msg_id) {
//	   Cursor cursor = getContent(msg_id);
//	   if(cursor != null){
//		   String creator = cursor.getString(cursor.getColumnIndex(MSG_CREATOR_ID));
//		   CurrentUser.readCurrentUserSettings(context);
//		   if(creator.equals(CurrentUser.getCurrentUsername())){
//			   //do nothing
//		   }
//		   else {
//			   //remove attachments related to this message id
//			   String[] attachment_arr = cursor.getString(cursor.getColumnIndex(MSG_EXTRA_CONTENT)).split(",");
//			   String[] type_arr = cursor.getString(cursor.getColumnIndex(MSG_EXTRA_TYPE)).split(",");
//			   if(attachment_arr.length == type_arr.length){
//				   for(String element : attachment_arr){
//					   File current_attachment = new File(element);
//					   if(current_attachment.delete()) {
//						   Log.d("REMOVE ATTACHMENT method: removeAttachment()", 
//								 "Attachment with name: " + element + " was correctly removed");
//						   return true;
//					   }
//				   }
//			   }
//			   
//		   }
//	   }
//	   else {
//		   Log.d("REMOVE ATTACHMENT method: removeAttachment()", "Message to remove not present in DB");
//		   return false;
//	   }
//	   return true;
//   }
//   // TODO USE THIS
//   public boolean removeAttachment(String msg_id){
//	   Cursor cursor = getContent(msg_id);
//	   if(cursor != null){
//		   //remove attachments related to this message id
//		   String[] attachment_arr = cursor.getString(cursor.getColumnIndex(MSG_EXTRA_CONTENT)).split(",");
//		   String[] type_arr = cursor.getString(cursor.getColumnIndex(MSG_EXTRA_TYPE)).split(",");
//		   if(attachment_arr.length == type_arr.length) {
//			   for(String element : attachment_arr){
//				   File current_attachment = new File(element);
//				   if(current_attachment.delete()) {
//					   Log.d("REMOVE ATTACHMENT method: removeAttachment()", 
//							 "Attachment with name: " + element + " was correctly removed");
//					   return true;
//				   }
//				   else {
//					   Log.e("removeAttachment() FAILED", 
//								 "Attachment with name: " + element + " NOT FOUND");
//					   return false;
//				   }
//			   }
//		   }
//	   }
//	   else {
//		   Log.d("REMOVE ATTACHMENT method: removeAttachment()", "Message to remove not present in DB");
//		   return false;
//	   }
//	   return true;
//   }
}
